/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package Modele;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashSet;
import java.util.TreeSet;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author sio
 */
public class DAO {

    public static Collection<Dep> getLesDeps() {

        Collection<Dep> lesDeps = new TreeSet();
        try {
            Connection con = Connect.get();
            Statement req;
            Statement req2;

            //Requete
            req = con.createStatement();
            req2 = con.createStatement();

            ResultSet rs = req.executeQuery("select distinct departement from medecin;");

            //Pour chaque numero de departement
            while (rs.next()) {
                String numDep = rs.getString("departement");
                Collection<Medecin> m = new TreeSet();

                ResultSet rs2 = req2.executeQuery("select * from medecin where departement='" + numDep + "';");
                //on recup chaque medecin du dep
                while (rs2.next()) {
                    Medecin medecin = new Medecin(rs2.getString("id"), rs2.getString("nom"), rs2.getString("prenom"),
                            rs2.getString("adresse"), rs2.getString("tel"), rs2.getString("specialitecomplementaire"));

                    m.add(medecin);
                }
                //on crée un new departement qu'on insere direct dans une collection de departement
                lesDeps.add(new Dep(numDep, m));
            }
            return lesDeps;

        } catch (Exception e) {
            System.out.println("Erreur dans le programme");
            e.printStackTrace();
        }
        return lesDeps;
    }

    public static Collection<Specialite> getLesSpe() {
        Collection<Specialite> lesSpe = new TreeSet();
        try {
            Connection con = Connect.get();
            Statement req;
            req = con.createStatement();
            ResultSet rs = req.executeQuery("select distinct specialitecomplementaire from medecin;");
            while (rs.next()) {
                if (rs.getString("specialitecomplementaire") != null) {
                    String nomspe = rs.getString("specialitecomplementaire");
                    lesSpe.add(new Specialite(nomspe));
                }
            }
            return lesSpe;
        } catch (Exception e) {
            System.out.println("Erreur dans le programme");
            e.printStackTrace();
        }
        return lesSpe;

    }

}
